#!/usr/bin/env python
# encoding: utf-8
#################
# Name:         自定义标签 tag_class_id=92数据处理 
# Purpose:
#
# Author:      stephen
#
# Created:     02/20/2019
# Licence:     1.0.0
#
#################
import logging
import ConfigParser
import re, os, random, sys, string, hashlib, math, uuid
import calendar
import json,struct
import numpy as np
import pymysql

#from datetime import *
import datetime
import time
from LOCAL_CONFIG import *
from phone import Phone
from birth import Birth

reload(sys)
sys.setdefaultencoding('utf-8')
sys.path.append(os.environ['HOME']+"/lib")
from pyctlib import *

VERSION_NUM  = '1.0.0'
VERSION_DATE = '2019-02-20'

class UniDB:
  def __init__(self,user,passwd,server,port,dbname,dbtype="Oracle"):
    self.__server = server
    self.__port   = port
    self.__dbname = dbname
    self.__user   = user
    self.__passwd = passwd
    self.__dbtype = dbtype.lower()
    self.dbconn   = None
  def __del__(self):
    pass
  def open(self):
    if self.__dbtype == "oracle":
      os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import cx_Oracle
      dsn_tns = cx_Oracle.makedsn(self.__server, self.__port  , self.__dbname)
      self.dbconn=cx_Oracle.connect(self.__user, self.__passwd, dsn_tns)
    elif self.__dbtype == "mysql":
      #os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import pymysql
      self.dbconn=pymysql.connect(host  =self.__server, port=self.__port, user=self.__user,\
                                  passwd=self.__passwd, db  =self.__dbname, charset='utf8')
    else:
      raise TypeError("OnlyOracle.")
    logging.info("DB[%s] Open."%self.__dbtype)
  def close(self):
    try:
      if self.dbconn:
        self.dbconn.close()
      self.dbconn=None
      logging.info("DB[%s]Closed."%self.__dbtype)
    except Exception,e:
      logging.info("DB[%s]ClosedExcept[%s]."%(self.__dbtype,e))
      self.dbconn=None
      
class UniCfg:
  def __init__(self,cfgfile):
    self.cfgfile=cfgfile
    self.load_cfg()
  def load_cfg(self):
    cf = ConfigParser.ConfigParser()
    cf.read(self.cfgfile)
    
    self.dbhost = cf.get("db"   , "dbhost")
    self.dbport = cf.getint("db", "dbport")
    self.dbname = cf.get("db"   , "dbname")
    self.dbuser = cf.get("db"   , "dbuser")
    self.dbpass = cf.get("db"   , "dbpass")
    self.dbtype = cf.get("db"   , "dbtype")
    
    self.logfile = cf.get("log","logfile")
    self.loglevel= cf.get("log","loglevel").upper()

class Handle:
  def __init__(self,udb=None,cfg=None):
    self.dbconn  = udb.dbconn
    self.cfg  = cfg

    self.__ph = Phone()
    self.add_user_id     = 1   
    self.update_user_id  = 1
    self.table           = 'usr_user_tag_relation' 
    self.__rules =[ 
     {'name':u'出生日期',                'table':'usr_user_basic_info', \
      'user_identify':'user_id',         'name_col':'left(birthday, 4)' },
     {'name':u'近30日累计签到次数',      'table':'yd_user_act_summary',\
      'user_identify':'user_key',        'name_col':'month1_app_scorehis_cnt'},
     {'name':u'近30日发布情报数',        'table':'yd_user_act_summary', \
      'user_identify':'user_key',        'name_col':'month1_app_pub_qingbao_cnt'}, 
     {'name':u'近30日评论文章数',        'table':'yd_user_act_summary', \
      'user_identify':'user_key',        'name_col':'month1_app_review_cnt'},
     {u'name':u'近30日打开APP次数',      'table':'yd_user_act_summary', \
      'user_identify':'user_key',        'name_col':'month1_app_open_cnt'},
     {u'name':u'近30日0-6点访问次数',    'table':'yd_user_act_summary', \
      'user_identify':'user_key',        'name_col':'month1_app_open026_cnt'},
     {u'name':u'近30日6-9点访问次数',    'table':'yd_user_act_summary',\
      'user_identify':'user_key',        'name_col':'month1_app_open629_cnt'},
     {u'name':u'近30日9-12点访问次数',   'table':'yd_user_act_summary',\
      'user_identify':'user_key',        'name_col':'month1_app_open9212_cnt'},
     {u'name':u'近30日12-14点访问次数',  'table':'yd_user_act_summary',\
      'user_identify':'user_key',        'name_col':'month1_app_open12214_cnt'},
     {u'name':u'近30日14-18点访问次数',  'table':'yd_user_act_summary',\
      'user_identify':'user_key',        'name_col':'month1_app_open14218_cnt'},
     {u'name':u'近30日18-22点访问次数',  'table':'yd_user_act_summary',\
      'user_identify':'user_key',        'name_col':'month1_app_open18222_cnt'},
     {u'name':u'近30日22-24点访问次数',  'table':'yd_user_act_summary',\
      'user_identify':'user_key',        'name_col':'month1_app_open22224_cnt'},
                 
     ] 

  def handle_data (self):
    try:
      self.__clean_user_tag()
      self.__handle_user_data()
    except  Exception,err:
      import traceback
      import StringIO
      fp = StringIO.StringIO()
      traceback.print_exc(file=fp)
      message = fp.getvalue()
      logging.info(message)
      logging.warning('err:%s %s' %(err, sys._getframe().f_back.f_code) )

  #清空数据库自定义的用户规则
  def __clean_usr_tag(self):
    sql = "delete from usr_user_tag_relation where tag_class_id=92"
    self.__update_db(sql)
  
  #处理数据
  def __handle_user_data(self):
   sql = "select * from usr_user_basic_info "
   res = self.__select_db(sql, True)
   for re in res:
     if not  re.get('user_id'):
       continue
     if re.get('user_id') <1:
       continue
     #logging.info('user_id:%s'%(re.get('user_id')) )
     self.__handle_data(re.get('user_id'))

 
  
  #根据用户的iD 处理所有的自定义标签规则 
  def __handle_data (self, user_id ):
    sql = "select *  from tg_tags_info  where \
           class_id=92  and state =1 "
           #class_id=92  and state =1 and tag_id=2833"

    res = self.__select_db(sql,True)
    for tag_info in res:
      if  tag_info.get('tag_rule') and tag_info.get('remark'):
        rule_dict = self.__generate_features_dict(tag_info)
        #logging.info(rule_dict)
        self.__handle_features_data(rule_dict, tag_info, user_id)

  # rule_dict 规则dict, tag_info 数据库规则信息数据 
  def __handle_features_data(self, rule_dict, tag_info, user_id):
    if user_id <1:
      return
    if type(rule_dict) !=dict  or   type(tag_info) != dict:
       return
    if not rule_dict.get('remark') or len(rule_dict.get('remark'))<1:
       return
    if not rule_dict.get('rules') or len(rule_dict.get('rules'))<1 or len(tag_info)<1:
       return
     
    if not tag_info.get('tag_id') or int(tag_info.get('tag_id')) <0:
      return
    if not tag_info.get('class_id') or int(tag_info.get('class_id'))<0:
      return
    if not tag_info.get('tag_name') or len(str(tag_info.get('tag_name')))<0:
      return

    #此规则 class_id, tag_id, 看用过是否打过标签                                              
    op_wh = "tag_class_id= %s and tag_id = %s  and user_id=%d "%(\
             tag_info.get('class_id'), tag_info.get('tag_id'), user_id)       
    op_cnt = self._table_cnt_count('usr_user_tag_relation', op_wh)           
    if op_cnt >0:                                                            
      return  

    
    #res_cnt 标签规则数量， res_list: 每个规则，存在数据的list 
    res_list=[]
    res_cnt =0 
    for rule in rule_dict.get('rules'):
      if rule.get('table') and rule.get('name_col') and rule.get('user_identify'):
        sql = "select %s from %s where %s %s %s and %s=%s "%( rule.get('user_identify'),\
             rule.get('table'), rule.get('name_col'), rule.get('RuleShip'), \
             rule.get('RuleCount'), rule.get('user_identify'), user_id)
        
        res = self.__select_db(sql, True)
        res_cnt += 1
        if not res or len(res)<1:
          continue
        res_list.append(res[0])

    if len(res_list)==0:
       return

    #add_dict 符合规则的操作数据库dict
    add_dict = {}
    add_dict['tag_id']       = tag_info.get('tag_id')
    add_dict['tag_class_id'] = tag_info.get('class_id')
    add_dict['tag_name']     = tag_info.get('tag_name')
    add_dict['user_id']      = user_id
   
    # 根据add_dict,生成sql, 插入用户自定义标签数据 
    if rule_dict.get('remark') == 'and': 
      if res_cnt == len(res_list): 
        if op_cnt ==0:
          sql = self.__generate_insert_sql(add_dict )    
          logging.info(sql)
          self.__update_db(sql) 

    if rule_dict.get('remark') == 'or':
      if res_cnt>0:
        sql = self.__generate_insert_sql(add_dict )
        logging.info(sql)
        self.__update_db(sql)

   	  

  # data_dict每条规则dict, 返回 {'remark':'and', 'rules':[]} 
  def __generate_features_dict(self, data_dict):
    if type(data_dict) != dict or len(data_dict)<0:
      return

    if not data_dict.get('tag_rule') or not data_dict.get('remark'):
      return
    if len(data_dict.get('tag_rule'))<1 or len(data_dict.get('remark'))<1:
      return  
    if (self.__rules) <1:
      return
    
    #数据库的规则 判断时候是[{}]格式，
    rule_db = eval( data_dict.get('tag_rule'))
    if type(rule_db) != list:
      return
    if len(rule_db) <1:
      return
 
    # and，需要每条规则有效 ,[{},{}]的每个{}规则，比较有数据, 并且程序的规则对应
    if str( data_dict.get('remark') ).lower() == 'and':   
      for rule in rule_db:
        if not rule.get('rule_name'):
          return
        if not self.__check_valid_prog_rule( rule.get('rule_name')):
          return
   
    #处理数据规格是 {'remark':'and', 'rules':[]} 
    rule_dict =  {'remark':str(data_dict.get('remark')).lower(), 'rules':[]}
    for rule in rule_db:
      if not  rule.get('rule_name'):
        continue 
      if not self.__check_valid_prog_rule( rule.get('rule_name')):
        continue 
       
      for _rule in self.__rules:
        if _rule.get('name') ==  rule.get('rule_name'):
          t={}
          t['user_identify'] = _rule.get('user_identify')
          t['table']         = _rule.get('table')
          t['name_col']      = _rule.get('name_col')
          t['RuleShip']      = rule.get('RuleShip')
          t['RuleCount']     = rule.get('RuleCount')      
          rule_dict['rules'].append( t )

    if len(rule_dict.get('rules'))>0:
      return rule_dict 
     
  
  def __check_valid_prog_rule(self, rule_name):
    if not rule_name or len(str(rule_name))<1:
      return False
 
    if type(rule_name) != unicode:
      rule_name = str(rule_name).decode('utf-8')
        
    for _rule in self.__rules:
      if _rule.get('name') == rule_name:
        return True     
    return False 

  #generate insert sql
  def __generate_insert_sql(self, add_dict):
    if type(add_dict) != dict or len(add_dict) <1: 
      return

    insert_sql = "insert into %s ( "%( self.table )
    values_sql = "values ("
    for key, val in add_dict.items():
      insert_sql += key + ','
      if type(val) == int:
        values_sql += '%d'%(val) + ','
      elif type(val) == float:
        values_sql += '%f'%(val) + ','                                       
      else:                                                                  
        values_sql += "'%s'"%(val) +','                                      
    insert_sql += "add_user_id, add_time" +','                               
    values_sql += "%d, '%s' "%(1, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) +','
    if insert_sql[-1] == ',':                                                
      insert_sql = insert_sql[:-1] + ') '                                    
    if values_sql[-1]==',':                                                  
      values_sql = values_sql[:-1] + ') '  

    sql = insert_sql + values_sql                                            
    return sql  
  

  #generate sql                                                              
  def __generate_update_sql(self, add_dict ):                                   
    if type(add_dict) != dict or len(add_dict) <1:                           
      return 
    if not add_dict.get('tag_id') or not add_dict.get('tag_class_id'):
      return
                                                                         
    wh = ''                                                                  
    update_key = ''                                                          
    if add_dict.has_key('mobile'):                                           
      wh += "where mobile = '%s'"%(add_dict['mobile'])                       
      update_key += 'mobile'                                                 
    else:                                                                    
      if add_dict.has_key('imei'):                                           
        wh += "where imei= '%s'"%(add_dict['imei'])                          
        update_key = 'imei'                                                  
    if len(wh) <1:                                                           
      return                                                                 
    if (update_key)<1:                                                       
      return 

    update_sql = "%s  set "%(table)
    for key, val in add_dict.items():
       
      if key == update_key:
        continue
      if type(val) == int:
        update_sql +=  '%s = %d'%(key, val) + ','
      elif type(val) == float:
        update_sql += ' %s = %f'%(key, val) + ','                            
      else:                                                                  
        update_sql += "%s = '%s'"%(key, val) +','                            
    update_sql += "update_user_id = %d, update_time='%s' "%(1, \
                 datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) +',' 
    if update_sql[-1]==',':                                                  
      update_sql = update_sql[:-1]                                           
    update_sql += wh 
                                                                             
    return update_sql
 
	  
  #check the colomn is or not to handle
  def is_valid_column(self, column):
    if column is None:
      return False
    d1 = ['real_name', 'mobile', 'idcard', 'email', 'user_sex', 'cur_age', \
      'birthday', 'constellation', 'blood_type', 'profession', 'company', \
      'education_degree', 'school', 'cet46', 'residential_province', \
      'residential_city', 'residential_district', 'residential_community', \
      'mobile_belong_province', 'mobile_belong_city', 'idcard_province', \
      'idcard_district', 'macid', 'imei', 'cellphone_brand', 'cellphone_model',\
      'cellphone_os_type', 'cellphone_os_version']

    for d in d1:
      if d == column:
        return True
    return False	  

  # return tag_name 
  def get_tag_name(self, column, value ):
    if column is None or value is None:
      return
    sql = "SELECT b.tag_name,  b.synonym  FROM `tg_tags_class_info` a, tg_tags_info b where \
         a.class_id= b.class_id  and a.rel_table=1 and a.rel_column='%s' "%(column)
    res = self.__select_db(sql,True)
    
    for row in res:
      #print(row)
      if not row.get('tag_name'):
        continue
      if row.get('synonym'):
        if row.get('synonym').find(',') != -1:
          cols = row.get('synonym').split(',')
          for col in cols:
            if col == value:                                                 
              return row.get('tag_name')                                     
        else:                                                                
          if row.get('synonym') == value:                                    
            return row.get('tag_name')                                       
                                                                             
      if row.get('tag_name') == value:                                       
        return  value 

  #mobile valid, return true or None    
  def is_valid_mobile(self, mobile):
    if mobile is None:
      return False
    mobile = str(mobile).strip()
    if  mobile.find('.') != -1:
      mobile = mobile[ :mobile.find('.')]
    if len(mobile) != 11 or not mobile.isdigit():
      return False
    #ph = Phone()
    if not self.__ph.find(mobile):
      return False
    else:
      return True


  #check imei is valid. 15 number
  def is_valid_imei(self, imei):
    if imei is None:
      return False
    imei = str(imei).strip()
    if imei.find('.') != -1:
      imei = imei[:imei.find('.')]
    if len(imei) == 15 and  imei.isdigit():
      return True

    return False


  # in dict all bytearray convert to unicode, check other  
  def  convert_data(self,  data=None):
    if data is None:
      raise TypeError( 'data error. %s'%(sys._getframe().f_back.f_code) ) 
    if type(data) == dict:
      # data format 
      for key, val  in data.items():
        #print('key:%s type:%s'%(key, type(val) ))
        if type(val) == bytearray:
          val = val.decode(encoding='utf8')
        if type(val) == unicode or type(val)==str:
          val = val.strip()
          if key == 'cur_age' or key=='mobile' or key=='birthday':
            if val.find('.') != -1:
              data[key] = val[:val.find('.') ]

      #data type
      for  key, val in data.items():
        if val is None:
          data.pop(key)
        if key  == 'birthday':
          if val is None or len(str(val) ) != 8 or not str(val).isdigit():
            data.pop(key)
            continue
          birth = Birth()
          if birth.get_age(val) is None:
            data.pop(key)
            continue


	  
	
  #通过select count 查询数据
  def _table_cnt_count(self,  table=None, wh=None ):
    if table == None:
      raise TypeError('tale name is errro')
    sql = "select count(1) from %s "%(table)
    if wh:
      sql +=  'where %s'%( wh)                                               
    #print(sql) 
    res = self.__select_db(sql)                                              
    #print( res )                                                            
    return res[0][0]  

  

  def __update_db(self, sql):
    try:
      cursor = self.dbconn.cursor()
      cursor.execute(sql)
      #logging.info(sql)
      self.dbconn.commit()
      cursor.close()
    except Exception,err:
      logging.warning('err:%s' %(err) )

  def __select_db(self, sql, dictionary=None):
    try:
      if dictionary:
        cursor = self.dbconn.cursor( pymysql.cursors.DictCursor )
      else:
        cursor = self.dbconn.cursor()                                        

      self.dbconn.commit()
      cursor.execute(sql )                                                    
      res = cursor.fetchall()                                                
      cursor.close()                                                         
      return res                                                             
    except Exception,err:                                                    
      logging.warning('err:%s' %(err) )  




   

def main():
   if len(sys.argv)<>3 or sys.argv[1]<>"-f":
     print "Run Like: %s -f %s.conf"%(sys.argv[0][:-3],sys.argv[0][:-3])
     exit(1)
   cfg_file = None
   if sys.argv[2].find('/')>=0:
     cfg_file=sys.argv[2].find('/')
   else:
     cfg_file=os.environ['HOME']+"/cfg/"+sys.argv[2]
   cfg = UniCfg(cfg_file)
   
   if cfg.loglevel == "DEBUG":
     iLogLevel = logging.DEBUG
   elif cfg.loglevel == "INFO":
     iLogLevel = logging.INFO
   elif cfg.loglevel == "WARN" or cfg.loglevel == "WARNING":
     iLogLevel = logging.WARNING
   else:
     iLogLevel = logging.ERROR
   logging.basicConfig( filename=cfg.logfile,
                        format  = "%(asctime)s %(levelname)s[%(process)d]:%(message)s",
                        level   = iLogLevel)
   
   logging.info("......START.........")
   UDB = UniDB(cfg.dbuser,cfg.dbpass,cfg.dbhost,cfg.dbport,cfg.dbname,cfg.dbtype)
   UDB.open()
   
   obj = Handle(UDB,cfg)
   obj.fetch_user()
   
   UDB.close()
   logging.info("......EXIT.........")
   logging.shutdown()
   return

if __name__ == "__main__":
  main()
